using ClosedXML.Excel;
using System.Data;
using System.Text.Json;

namespace SPI.AgentServer.Services.Export;

/// <summary>
/// Excel 导出服务实现
/// 使用 ClosedXML 库（开源免费）
/// </summary>
public class ExcelExportService : IExportService
{
    private readonly ILogger<ExcelExportService> _logger;

    public ExcelExportService(ILogger<ExcelExportService> logger)
    {
        _logger = logger;
    }

    /// <summary>
    /// 导出为 Excel 文件
    /// </summary>
    public async Task<ExportResult> ExportToExcelAsync(object data, string fileName, string? sheetName = null)
    {
        return await Task.Run(() =>
        {
            try
            {
                using var workbook = new XLWorkbook();
                var worksheet = workbook.Worksheets.Add(sheetName ?? "Sheet1");

                // 将数据转换为 DataTable
                var dataTable = ConvertToDataTable(data);

                // 填充数据到工作表
                if (dataTable.Rows.Count > 0)
                {
                    // 添加表头
                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        worksheet.Cell(1, i + 1).Value = dataTable.Columns[i].ColumnName;
                        worksheet.Cell(1, i + 1).Style.Font.Bold = true;
                        worksheet.Cell(1, i + 1).Style.Fill.BackgroundColor = XLColor.LightGray;
                    }

                    // 添加数据
                    for (int row = 0; row < dataTable.Rows.Count; row++)
                    {
                        for (int col = 0; col < dataTable.Columns.Count; col++)
                        {
                            var value = dataTable.Rows[row][col];
                            var cellValue = TruncateForExcel(value?.ToString() ?? "");
                            worksheet.Cell(row + 2, col + 1).Value = cellValue;
                        }
                    }

                    // 自动调整列宽
                    worksheet.Columns().AdjustToContents();

                    // 添加筛选器
                    worksheet.RangeUsed()?.SetAutoFilter();
                }
                else
                {
                    worksheet.Cell(1, 1).Value = "无数据";
                }

                // 保存到内存流
                var stream = new MemoryStream();
                workbook.SaveAs(stream);
                stream.Position = 0;

                _logger.LogInformation("Excel 文件生成成功：{FileName}，大小：{Size} 字节", 
                    fileName, stream.Length);

                return new ExportResult
                {
                    Stream = stream,
                    ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                    FileName = $"{fileName}.xlsx"
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导出 Excel 失败");
                throw;
            }
        });
    }

    /// <summary>
    /// 导出为 Excel（多工作表）
    /// </summary>
    public async Task<ExportResult> ExportToExcelMultiSheetAsync(Dictionary<string, object> sheets, string fileName)
    {
        return await Task.Run(() =>
        {
            try
            {
                using var workbook = new XLWorkbook();

                foreach (var sheet in sheets)
                {
                    var worksheet = workbook.Worksheets.Add(sheet.Key);
                    var dataTable = ConvertToDataTable(sheet.Value);

                    if (dataTable.Rows.Count > 0)
                    {
                        // 添加表头
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            worksheet.Cell(1, i + 1).Value = dataTable.Columns[i].ColumnName;
                            worksheet.Cell(1, i + 1).Style.Font.Bold = true;
                            worksheet.Cell(1, i + 1).Style.Fill.BackgroundColor = XLColor.LightGray;
                        }

                        // 添加数据
                        for (int row = 0; row < dataTable.Rows.Count; row++)
                        {
                        for (int col = 0; col < dataTable.Columns.Count; col++)
                        {
                            var value = dataTable.Rows[row][col];
                            var cellValue = TruncateForExcel(value?.ToString() ?? "");
                            worksheet.Cell(row + 2, col + 1).Value = cellValue;
                        }
                        }

                        worksheet.Columns().AdjustToContents();
                        worksheet.RangeUsed()?.SetAutoFilter();
                    }
                }

                var stream = new MemoryStream();
                workbook.SaveAs(stream);
                stream.Position = 0;

                _logger.LogInformation("多工作表 Excel 文件生成成功：{FileName}，大小：{Size} 字节", 
                    fileName, stream.Length);

                return new ExportResult
                {
                    Stream = stream,
                    ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                    FileName = $"{fileName}.xlsx"
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导出多工作表 Excel 失败");
                throw;
            }
        });
    }

    /// <summary>
    /// 导出为 CSV 文件
    /// </summary>
    public async Task<ExportResult> ExportToCsvAsync(object data, string fileName)
    {
        return await Task.Run(() =>
        {
            try
            {
                var dataTable = ConvertToDataTable(data);
                var csv = new System.Text.StringBuilder();

                // 添加表头
                csv.AppendLine(string.Join(",", dataTable.Columns.Cast<DataColumn>().Select(c => EscapeCsv(c.ColumnName))));

                // 添加数据
                foreach (DataRow row in dataTable.Rows)
                {
                    csv.AppendLine(string.Join(",", row.ItemArray.Select(field => EscapeCsv(field?.ToString() ?? ""))));
                }

                var bytes = System.Text.Encoding.UTF8.GetBytes(csv.ToString());
                var stream = new MemoryStream(bytes);

                _logger.LogInformation("CSV 文件生成成功：{FileName}，大小：{Size} 字节", 
                    fileName, stream.Length);

                return new ExportResult
                {
                    Stream = stream,
                    ContentType = "text/csv",
                    FileName = $"{fileName}.csv"
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "导出 CSV 失败");
                throw;
            }
        });
    }

    /// <summary>
    /// 转换对象为 DataTable
    /// </summary>
    private DataTable ConvertToDataTable(object data)
    {
        var dataTable = new DataTable();

        try
        {
            // 优先处理匿名对象 { columns, rows }
            var dataType = data.GetType();
            var columnsProperty = dataType.GetProperty("columns");
            var rowsProperty = dataType.GetProperty("rows");
            
            if (columnsProperty != null && rowsProperty != null)
            {
                _logger.LogDebug("检测到 columns/rows 结构，开始转换");
                
                var columns = columnsProperty.GetValue(data) as System.Collections.IList;
                var rows = rowsProperty.GetValue(data) as System.Collections.IList;
                
                if (columns != null && rows != null)
                {
                    // 添加列
                    foreach (var col in columns)
                    {
                        dataTable.Columns.Add(col?.ToString() ?? "");
                    }
                    
                    // 添加行（每行是Dictionary<string, object>）
                    foreach (var rowObj in rows)
                    {
                        if (rowObj is System.Collections.IDictionary dict)
                        {
                            var dataRow = dataTable.NewRow();
                            for (int i = 0; i < dataTable.Columns.Count; i++)
                            {
                                var columnName = dataTable.Columns[i].ColumnName;
                                if (dict.Contains(columnName))
                                {
                                    dataRow[i] = dict[columnName] ?? DBNull.Value;
                                }
                                else
                                {
                                    dataRow[i] = DBNull.Value;
                                }
                            }
                            dataTable.Rows.Add(dataRow);
                        }
                    }
                    
                    _logger.LogDebug("转换完成：{RowCount}行，{ColumnCount}列", 
                        dataTable.Rows.Count, dataTable.Columns.Count);
                    
                    return dataTable;
                }
            }
            
            // 处理 JsonElement 类型（从查询结果转换而来）
            if (data is JsonElement jsonElement)
            {
                if (jsonElement.ValueKind == JsonValueKind.Array)
                {
                    var array = jsonElement.EnumerateArray().ToList();
                    if (array.Count == 0)
                    {
                        return dataTable; // 返回空表
                    }

                    // 从第一行获取列名
                    var firstRow = array[0];
                    if (firstRow.ValueKind == JsonValueKind.Object)
                    {
                        foreach (var prop in firstRow.EnumerateObject())
                        {
                            dataTable.Columns.Add(prop.Name);
                        }

                        // 添加所有行数据
                        foreach (var item in array)
                        {
                            var row = dataTable.NewRow();
                            foreach (var prop in item.EnumerateObject())
                            {
                                row[prop.Name] = GetJsonValue(prop.Value);
                            }
                            dataTable.Rows.Add(row);
                        }
                    }
                }
                else if (jsonElement.ValueKind == JsonValueKind.Object)
                {
                    // 单个对象，转为单行
                    foreach (var prop in jsonElement.EnumerateObject())
                    {
                        dataTable.Columns.Add(prop.Name);
                    }

                    var row = dataTable.NewRow();
                    foreach (var prop in jsonElement.EnumerateObject())
                    {
                        row[prop.Name] = GetJsonValue(prop.Value);
                    }
                    dataTable.Rows.Add(row);
                }
            }
            // 处理字典类型
            else if (data is System.Collections.IDictionary dict)
            {
                foreach (var key in dict.Keys)
                {
                    dataTable.Columns.Add(key.ToString());
                }

                var row = dataTable.NewRow();
                foreach (var key in dict.Keys)
                {
                    row[key.ToString()!] = dict[key];
                }
                dataTable.Rows.Add(row);
            }
            // 处理列表类型
            else if (data is System.Collections.IEnumerable enumerable && data is not string)
            {
                var list = enumerable.Cast<object>().ToList();
                if (list.Count == 0)
                {
                    return dataTable;
                }

                // 使用反射获取属性
                var props = list[0].GetType().GetProperties();
                foreach (var prop in props)
                {
                    dataTable.Columns.Add(prop.Name);
                }

                foreach (var item in list)
                {
                    var row = dataTable.NewRow();
                    foreach (var prop in props)
                    {
                        row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                    }
                    dataTable.Rows.Add(row);
                }
            }
            else
            {
                // 尝试 JSON 序列化再反序列化
                var json = JsonSerializer.Serialize(data);
                var jsonDoc = JsonDocument.Parse(json);
                return ConvertToDataTable(jsonDoc.RootElement);
            }
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "转换数据为 DataTable 失败");
            throw;
        }

        return dataTable;
    }

    /// <summary>
    /// 从 JsonElement 获取实际值
    /// </summary>
    private object GetJsonValue(JsonElement element)
    {
        return element.ValueKind switch
        {
            JsonValueKind.String => element.GetString() ?? "",
            JsonValueKind.Number => element.TryGetInt64(out var l) ? l : element.GetDouble(),
            JsonValueKind.True => true,
            JsonValueKind.False => false,
            JsonValueKind.Null => DBNull.Value,
            _ => element.ToString()
        };
    }

    /// <summary>
    /// 截断过长的字符串，避免超过Excel单元格限制（32,767字符）
    /// </summary>
    private string TruncateForExcel(string value)
    {
        const int maxLength = 32700; // 留一些余量
        
        if (string.IsNullOrEmpty(value))
            return value;
        
        if (value.Length <= maxLength)
            return value;
        
        // 截断并添加提示
        _logger.LogWarning("单元格内容过长({Length}字符)，已截断到{MaxLength}字符", 
            value.Length, maxLength);
        
        return value.Substring(0, maxLength) + "...[内容过长已截断]";
    }

    /// <summary>
    /// 转义 CSV 字段
    /// </summary>
    private string EscapeCsv(string value)
    {
        // CSV也需要截断
        value = TruncateForExcel(value);
        
        if (value.Contains(',') || value.Contains('"') || value.Contains('\n'))
        {
            return $"\"{value.Replace("\"", "\"\"")}\"";
        }
        return value;
    }
}

